<?php

namespace tmcore\utils;

use tmcore\traits\ErrorTrait;
use think\Exception;
use think\facade\Db;

/**
 * Sql文件
 * @package tmcore\utils
 */
class Sql {

    use ErrorTrait;

    /**
     * 执行sql文件
     * @param string $sql_path sql路径
     * @return bool
     */
    public static function execute_sql($sql_path) {
        $db_instance = Db::connect("mysql");
        try {
            // 开始安装
            $sqlData = self::get_mysql_data($sql_path, '', '');
            if(!$sqlData) throw new \Exception(self::getError("拼装sql有误"));
            if($sqlData && is_array($sqlData)) {
                foreach ($sqlData as $sql) {
                    $db_instance->execute($sql);
                }
            }

            return true;
        } catch (\Exception $e) {
            return self::setError($e->getMessage());
        }
    }

    /**
     * 拼装sql语句
     * @param string $sql_path sql文件路径
     * @param string $old_prefix 原表前缀
     * @param string $new_prefix 新表前缀
     * @param string $separator 分隔符 参数可为";\n"或";\r\n"或";\r"
     * @return array|bool
     */
    public static function get_mysql_data($sql_path, $old_prefix = "", $new_prefix = "", $separator = ";\n")
    {
        $commenter = ['#', '--'];
        //判断文件是否存在
        if (!file_exists($sql_path)) return self::setError("找不到sql文件");

        $content = file_get_contents($sql_path);   //读取sql文件
        $content = str_replace(array($old_prefix, "\r"), array($new_prefix, "\n"), $content);//替换前缀

        //通过sql语法的语句分割符进行分割
        $segment = explode($separator, trim($content));

        //去掉注释和多余的空行
        $data = array();
        foreach ($segment as $statement) {
            $sentence = explode("\n", $statement);
            $newStatement = array();
            foreach ($sentence as $subSentence) {
                if ('' != trim($subSentence)) {
                    //判断是会否是注释
                    $isComment = false;
                    foreach ($commenter as $comer) {
                        if (preg_match("/^(" . $comer . ")/is", trim($subSentence))) {
                            $isComment = true;
                            break;
                        }
                    }
                    //如果不是注释，则认为是sql语句
                    if (!$isComment)
                        $newStatement[] = $subSentence;
                }
            }
            if(count($newStatement) == 1) {
                if(self::check_sql($newStatement[0])) {
                    $data[] = $newStatement;
                } else {
                    return self::setError(self::getError("检查sql有误"));
                }
            } else {
                $data[] = $newStatement;
            }
        }

        $result = [];
        //组合sql语句
        foreach ($data as $statement) {
            $newStmt = '';
            foreach ($statement as $sentence) {
                $newStmt = $newStmt . trim($sentence) . "\n";
            }
            if (!empty($newStmt)) {
                $result[] = $newStmt;
            }
        }
        return $result;
    }

    /**
     * 检验sql(只可检验字段)
     * @param string $sql 执行的sql语句
     * @return bool
     */
    public static function check_sql($sql) {

        $db_instance = Db::connect("mysql");

        //匹配多空格为单空格
        $sql = preg_replace("/[\s]+/is"," ",trim($sql));

        $data = explode(" ",$sql); //拆分数组

        if (strpos($sql, 'ALTER TABLE') !== false && strpos($sql, 'ADD INDEX') !== false) {
            //添加索引
            $table_name = trim($data[2],'`'); //表名
            $field_name = substr($data[5], 0, strrpos($data[5], '(')); //字段名

            $Fields = $db_instance->getTableFields($table_name);
            if(!in_array($field_name,$Fields))return self::setError('添加索引'.$field_name."字段不存在");

        } else if (strpos($sql, 'DROP INDEX') !== false) {
            //删除索引
            $table_name = trim($data[4],'`'); //表名
            $field_name = $data[2]; //字段名

            $Fields = $db_instance->getTableFields($table_name);
            if(!in_array($field_name,$Fields))return self::setError('删除索引'.$field_name."字段不存在");

        } else if (strpos($sql, 'ALTER TABLE') !== false && strpos($sql, 'ADD COLUMN') !== false) {
            //添加字段
            $table_name = trim($data[2],'`'); //表名
            $field_name = $data[5]; //字段名

            $Fields = $db_instance->getTableFields($table_name);
            if(in_array($field_name,$Fields))return self::setError('添加字段'.$field_name."字段已存在");

        } else if (strpos($sql, 'ALTER TABLE') !== false && strpos($sql, 'DROP COLUMN') !== false) {
            //删除字段

            $table_name = trim($data[2],'`'); //表名
            $field_name = $data[5]; //新字段名

            $Fields = $db_instance->getTableFields($table_name);
            if(!in_array($field_name,$Fields))return self::setError('删除字段'.$field_name."字段不存在");

        } else if (strpos($sql, 'ALTER TABLE') !== false && strpos($sql, 'CHANGE') !== false) {
            //修改字段

            $table_name = trim($data[2],'`'); //表名
            $field_name = $data[4]; //原字段名

            $Fields = $db_instance->getTableFields($table_name);
            if(!in_array($field_name,$Fields))return self::setError('修改字段'.$field_name."字段不存在");
        }

        return true;
    }

}